﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;
using System.IO;
public partial class BCThuTheoKhachHang : System.Web.UI.Page
{

    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    string frmName = "frmThuTheoKH";
    static int f_SuDung = 0, f_Them = 0, f_Sua = 0, f_Xoa = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static System.Data.DataTable dt;
    static int PageNumber = 5;
    static int CurrentPage = 1;
    double tongphaitravat;
    double tongphaitrakhongvat;
    int pc;
    int loaicongno;
    float sotien;
    static DataTable dtPrint;
    NpoiExport cl = new NpoiExport();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])))
            gstGetMess("Hết phiên làm việc, vui lòng đăng nhập để tiếp tục", "Default.aspx");
        else
        {
            if (!IsPostBack)
            {

                string idnguoidung = Session["IDUser"].ToString();
                string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

                System.Data.DataTable dt2 = ex.GetData_Text(sql);
                if (dt2.Rows.Count > 0)
                {
                    f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                    f_Them = int.Parse(dt2.Rows[0]["Them"].ToString());
                    f_Sua = int.Parse(dt2.Rows[0]["Sua"].ToString());
                    f_Xoa = int.Parse(dt2.Rows[0]["Xoa"].ToString());
                    f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                    f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
                }
                if (f_SuDung.Equals(1))
                {
                    Title = "Báo cáo phiếu thu ngân hàng theo khách hàng | " + Header;

                    btnCancel_Click(sender, e);
                    for (int i = 1; i <= 12; i++)
                    {
                        dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
                    }
                    dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
                    dlThang.SelectedIndex = 0;

                    int year1 = DateTime.Now.Year - 3;
                    int year2 = DateTime.Now.Year;
                    for (int i = year2; i >= year1; i--)
                    {
                        dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
                    }
                    dlNam.SelectedValue = year2.ToString();

                    txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
                    txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
                    dlNam.Visible = false;

                    LoadNCCTK();

                    

                }
                else
                    gstGetMess("Bạn không có quyền xem trang này", "Home.aspx");

            }
        }
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GVPhieuChi.DataSource = ex.GetData("sp_BaoCaoPhieuthu_List", p);
        GVPhieuChi.DataBind();
        dt = ex.GetData("sp_BaoCaoPhieuthu_List_Footer");
        LoadFooter();
        Button1.Visible = true;
        Button2.Visible = true;
        Button3.Visible = true;
    }

    private void LoadFooter()
    {
        string[] field = { "sotien" };
        if (dt.Rows.Count > 0)
        {
            GVPhieuChi.FooterRow.Cells[2].Text = "Tổng cộng";
            GVPhieuChi.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
            for (int k = 3; k < 4; k++)
            {
                GVPhieuChi.FooterRow.Cells[k].Text = ConvertDecimal(double.Parse(dt.Compute("sum(" + field[k - 3] + ")", "").ToString()));
            }


        }
    }

    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }



    void LoadNCCTK()
    {
        getDate();
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        dlnhacungcap.Items.Clear();
        dlnhacungcap.DataSource = ex.GetData("[sp_BaoCaoPhieuthuTheoKhachHang_ListNhanVien]", p);
        dlnhacungcap.DataBind();
        dlnhacungcap.Items.Insert(0, new ListItem("--Chọn họ tên--", ""));
        dlnhacungcap.SelectedIndex = 0;
    }







    string ConvertDecimal(double s)
    {
        string q = "";
        if (s.ToString().Contains("."))
        {
            double x = double.Parse(s.ToString().Substring(0, s.ToString().IndexOf('.')));
            q = (x != 0 && x > 999) ? string.Format("{0:0,0}", x) : x.ToString();
            q += s.ToString().Substring(s.ToString().IndexOf('.'));
        }
        else
            q = (s != 0 && s > 999) ? string.Format("{0:0,0}", s) : s.ToString();
        return q;
    }


    void LoadFooterGVPhieuChi()
    {
        double sonhienlieu = 0;
        for (int i = 0; i < GVPhieuChi.Rows.Count; i++)
        {

            sonhienlieu += double.Parse(GVPhieuChi.Rows[i].Cells[3].Text.Replace(",", ""));


        }

        if (GVPhieuChi.Rows.Count > 0)
        {
            GVPhieuChi.FooterRow.Cells[1].Text = "Tổng số";
            GVPhieuChi.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Center;
            GVPhieuChi.FooterRow.Cells[3].Text = sonhienlieu > 999 ? string.Format("{0:0,0}", sonhienlieu) : sonhienlieu.ToString();



        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GVPhieuChi.DataSource = ex.GetData("sp_BaoCaoPhieuthu_List", p);
        GVPhieuChi.DataBind();
        dt = ex.GetData("sp_BaoCaoPhieuthu_List_Footer");
        LoadFooter();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;


            p = new SqlParameter[2];
            p[0] = new SqlParameter("@PageNumber", CurrentPage);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@RowspPage", PageNumber);
            p[1].SqlDbType = SqlDbType.Int;

            GVPhieuChi.DataSource = ex.GetData("sp_BaoCaoPhieuthu_List", p);
            GVPhieuChi.DataBind();
            dt = ex.GetData("sp_BaoCaoPhieuthu_List_Footer");
            LoadFooter();


        }
        else
        {
            Button1.Enabled = false;
            Button2.Enabled = false;
            Button3.Enabled = true;
        }
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        CurrentPage++;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GVPhieuChi.DataSource = ex.GetData("sp_BaoCaoPhieuthu_List", p);
        GVPhieuChi.DataBind();

        dt = ex.GetData("sp_BaoCaoPhieuthu_List_Footer");
        LoadFooter();
        if (GVPhieuChi.Rows.Count.Equals(0))
        {
            Button2.Enabled = true;
            Button3.Enabled = false;
        }
        else
        {
            Button2.Enabled = true;
            Button3.Enabled = true;
        }
        Button1.Enabled = true;
    }
    protected void btnXemPC_Click(object sender, EventArgs e)
    {
        getDate();
        if (dlnhacungcap.SelectedIndex == 0)
        {
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;



            GVPhieuChi.DataSource = ex.GetData("sp_BaoCaoPhieuthuTheoKhachHang_List_TheoThoiGian", p);
            GVPhieuChi.DataBind();
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            dt = ex.GetData("sp_BaoCaoPhieuthuTheoKhachHang_List_TheoThoiGian", p);
            LoadFooter();
            GVPhieuChi.Visible = true;

            Button2.Visible = false;
            Button3.Visible = false;
            Button1.Visible = false;
        }
        else
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;


            p[2] = new SqlParameter("@HoTen", ex.GetGuid(dlnhacungcap.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            GVPhieuChi.DataSource = ex.GetData("[sp_BaoCaoPhieuthuTheoKhachHang_List_TheoThoiGian_HoTen]", p);
            GVPhieuChi.DataBind();
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@HoTen", ex.GetGuid(dlnhacungcap.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            dt = ex.GetData("[sp_BaoCaoPhieuthuTheoKhachHang_List_TheoThoiGian_HoTen]", p);
            LoadFooter();
            GVPhieuChi.Visible = true;

            Button2.Visible = false;
            Button3.Visible = false;
            Button1.Visible = false;

        }
        GVPhieuChi.Visible = true;
        Button1.Visible = false;
        Button2.Visible = false;
        Button3.Visible = false;
    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }


    static public void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            throw new Exception("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        LoadNCCTK();

        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
        }
    }
    protected void btnIn_Click(object sender, EventArgs e)
    {
        getDate();


        if (dlnhacungcap.SelectedIndex == 0 )
        {
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;

            dtPrint = ex.GetData("sp_BaoCaoPhieuthuTheoKhachHang_List_TheoThoiGian_Print", p);

        }
       
        PrintNPOI(dtPrint);
    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "Bảng tổng hợp";
            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text + " " + dlNam.SelectedItem.Text : "Từ " + txtTuNgay.Text + " Đến " + txtDenNgay.Text;
            //  string congtrinh = dlCongTrinh.SelectedIndex > 0 ? " - " + dlCongTrinh.SelectedItem.Text : "";
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Báo Cáo Thu Theo Khách Hàng " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            //khachhang
            //rowIndex++;
            //row = sheet.CreateRow(rowIndex);
            //r1c1 = row.CreateCell(0);
            //r1c1.SetCellValue(dlKhachHang.SelectedIndex > 0 ? "Khách hàng: " + dlKhachHang.SelectedItem.Text + congtrinh : dlLoaiKH.SelectedIndex > 0 ? "Loại khách hàng: " + dlLoaiKH.SelectedItem.Text : "");
            //r1c1.CellStyle = hs3;
            //r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 12);
            sheet.AddMergedRegion(cra);

            //freeze panes

            #endregion
            //End header-----------------------------------------------------------------------

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Ngày tháng", "Nội dung", "Khách hàng", "Số tiền", "Tài Khoản-Ngân Hàng"
                              };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;

                sheet.AutoSizeColumn(j);

            }

            #endregion

            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j <= dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 4)
                    {
                        if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                        }
                        else
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                        if (j <= 1)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }


            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(2);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            string[] footer = { "E" };
            for (int k = 4; k <= 4; k++)
            {

                cell = row.CreateCell(k);
                cell.SetCellValue(GVPhieuChi.FooterRow.Cells[k-1].Text.Trim().Equals("&nbsp;") ? "0" : GVPhieuChi.FooterRow.Cells[k-1].Text);
                cell.Row.Height = 500;
                cell.CellStyle = hs4;

            }
            //   fix lai border
            r1c1 = row.CreateCell(0);
            r1c1.CellStyle = hs4;
            r1c1.Row.Height = 400;

            r1c1 = row.CreateCell(1);
            r1c1.CellStyle = hs4;
            r1c1.Row.Height = 400;
            r1c1 = row.CreateCell(3);
            r1c1.CellStyle = hs4;
            r1c1.Row.Height = 400;

            r1c1 = row.CreateCell(5);
            r1c1.CellStyle = hs4;
            r1c1.Row.Height = 400;


            for (int ik = 0; ik < 6; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }


            //end sheet tong hop--------------------------

            //in tung tai khoan ngan hang
            #region taikhoan
            string tenkhachhang = "";
            if (dlnhacungcap.SelectedIndex.Equals(0))
            {

                int STT = 0;
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        tenkhachhang = dt.Rows[i]["tenkhachhang"].ToString();

                        if (!tenkhachhang.Equals(dt.Rows[i - 1]["tenkhachhang"].ToString()))
                        {
                            STT = 0;
                            //-----------Footer
                            #region Footer

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(2);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int k = 4; k < 5; k++)
                            {

                                cell = row.CreateCell(k);
                                cell.CellFormula = "Sum(" + footer[k - 4] + "6:" + footer[k - 4] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;

                            }

                            for (int h = 0; h < 6; h++)
                            {
                                if (h == 0 || h == 1 || h == 3|| h == 5)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            for (int ik = 0; ik <= dt.Columns.Count; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }
                            #endregion

                            //---End Footer

                            sheet = workbook.CreateSheet(cl.EscapeSheetName(tenkhachhang));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------

                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Báo Cáo Thu Khách Hàng " + tenkhachhang + " " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);



                            //freeze panes

                            #endregion
                            #region Header1-2
                            rowIndex++;
                            //header1


                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 1; j <= header.Length; j++)
                            {
                                cell = row.CreateCell(j);
                                cell.SetCellValue(header[j - 1].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                            STT++;

                        }
                    }
                    else
                    {
                        tenkhachhang = "";
                        if (!tenkhachhang.Equals(dt.Rows[i]["tenkhachhang"].ToString()))
                        {
                            tenkhachhang = dt.Rows[i]["tenkhachhang"].ToString();
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(tenkhachhang));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Báo Cáo Thu Khách Hàng " + tenkhachhang + " " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //nha cung cap



                            //freeze panes

                            #endregion

                            #region Header1-2
                            rowIndex++;
                            //header1

                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 1; j <= header.Length; j++)
                            {
                                cell = row.CreateCell(j);
                                cell.SetCellValue(header[j - 1].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                        }
                        for (int ik = 0; ik <= dt.Columns.Count - 1; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                    #region ghidulieu
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue((STT).ToString());
                    cell.CellStyle = hsCenter;

                    for (int j = 1; j <= dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);
                        if (j == 4)
                        {
                            if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                            }
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                            if (j == 3)
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }
                    #endregion
                    if (i == dt.Rows.Count - 1)
                    {
                        #region Footer

                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(2);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        for (int k = 4; k < 5; k++)
                        {

                            cell = row.CreateCell(k);
                            cell.CellFormula = "Sum(" + footer[k - 4] + "6:" + footer[k - 4] + rowIndex.ToString() + ")";
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                        }

                        // fix lai border
                        for (int h = 0; h < 6; h++)
                        {
                            if (h == 0 || h == 1 || h == 3 || h == 5)
                            {
                                r1c1 = row.CreateCell(h);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                        }
                        for (int ik = 0; ik <= dt.Columns.Count; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                        #endregion}
                    }
                }
            #endregion

                string ngaythang1 = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
                using (var exportData = new MemoryStream())
                {
                    workbook.Write(exportData);
                    
                    string saveAsFileName = "BCThuTheoKH-"  + " " + ngaythang1 + ".xls";

                    Response.ContentType = "application/vnd.ms-excel";
                    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                    Response.Clear();
                    Response.BinaryWrite(exportData.GetBuffer());
                    Response.End();

                }
            }
        }
    }
}